The Database tab defines the connection between physical tables and views within the iMIS database and the properties of the business object. The properties of a business object can comprise single or multiple tables and views. Where multiple tables and views are defined, an appropriate set of inner joins must be defined.
By default, the BOD Database tab is found in the following location:
■ Tools > Business object designer, then from the toolbar choose New > Design Business Definition (or select an existing object, then from the toolbar choose Edit), then select the Database tab.
Tables
Specifies which tables from the iMIS database are used as the object’s data source, similar to the FROM clause of an SQL SELECT statement. To specify the tables in this list, use Add and Remove. The first table that you add is automatically designated as (Primary), which has no system effect and is meant only to indicate which table was added first to the object.
Used Columns
Specifies which columns from the tables specified in the Tables list are used as the object's properties, similar to specifying column names in the SELECT clause of a SELECT statement. The columns that you select here are listed on the Properties tab. To specify the columns in this list, select one or more columns from the Available Columns list, then use Add as Properties (hold Ctrl or Shift to select multiple columns).
Filter Expression
(optional) Specifies a filter expression that further limits the table rows that are acted upon by this business object, similar to the WHERE clause of an SQL SELECT statement. The filter expression is applied after the result set of any Joins specified for this object. You must omit the WHERE keyword itself. If there is more than one table listed in the Tables list, you must use qualified column names to prevent ambiguity, as shown in the following example using the ACTIVITY_TYPE column from the Activity table:
Activity.ACTIVITY_TYPE=‘F’
You must specify only a single WHERE clause, because multiple tables listed in the Tables field are joined with an inner join
Available Columns
Lists all of the columns that are available for use as properties for this object. The list comprises all columns from all tables specified in the Tables list, minus the columns that have already been added to the Used Columns list.
Joins
(required if more than one table is specified in the Tables list) Specifies inner joins between the tables specified in the Tables list, which limits the table rows on which this business object acts to the result set created by the joins. The drop down lists enable you to choose the columns for each join, then Add adds the join to the list of joins for this object.
Multiple joins between the same two tables are expressed with a Boolean AND operator in the same INNER JOIN clause. Every join between a different pair of tables is expressed as a separate INNER JOIN clause.
For example, assume that you've added Table1, Table 2, and Table3 to the Tables list, and you have added all of the Available Columns to the Used Columns list. You then add the following three joins to the Joins list.
Table1.Column1 = Table2.Column1
Table1.Column2 = Table2.Column2
Table2.Column3 = Table3.Column3
In this case, an analogous SELECT statement would be:
SELECT *
FROM Table1
INNER JOIN Table2
ON Table1.Column1 = Table2Column.1
AND Table1.Column2 = Table2.Column2
INNER JOIN Table3
ON = Table2.Column3 = Table3.Column3
BOD does not support the creation of cross joins, outer joins, or any join type other than an inner join. The joins that you define here are available as default joins when using the object in an Intelligent Query Architect (IQA) query, but IQA also gives the option to create different, custom joins for the tables underlying the business objects used in the query.